﻿using System;
using System.Linq;
using System.Web;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using Bamuca.Codigo.DTO;
using System.Data;

namespace Bamuca.Codigo.DAO
{
    public class EscolaDao
    {
        MySqlConnection conn = ConexaoDao.SqlConectar();
        MySqlCommand cmd = new MySqlCommand();

        public void Inserir(EscolaDto _escolaDto)
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = "insert into escolas (nome, status)" +
                                  "values (@nome, @status)";
                cmd.Parameters.AddWithValue("@nome", _escolaDto.Nome);
                cmd.Parameters.AddWithValue("@status", _escolaDto.Status);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }

        public void Alterar(EscolaDto _escolaDto)
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = "UPDATE escolas SET (nome = @nome, " +
                                   "status = @status " +
                                   "WHERE idescola = @id";

                cmd.Parameters.AddWithValue("@nome", _escolaDto.Nome);
                cmd.Parameters.AddWithValue("@status", _escolaDto.Status);
                cmd.Parameters.AddWithValue("@id", _escolaDto.IdEscola);

                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }

        }

        public void Excluir(EscolaDto _escolaDto)
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = "delete escolas WHERE idescola = @id";
                cmd.Parameters.AddWithValue("@id", _escolaDto.IdEscola);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }

        }

        public DataTable ConsultarTodosPorParametro(EscolaDto _escolaDto)
        {
            MySqlDataAdapter dtAdap;
            DataTable dtTable = new DataTable();

            string _strSql = "SELECT idescola AS Id, Nome, IF(status = 1, 'Ativo', 'Inativo') AS Status FROM escolas WHERE 1=1 ";
            if (_escolaDto.IdEscola > 0)
            {
                _strSql = _strSql + "AND idescola = " + _escolaDto.IdEscola + " ";
            }
            if (_escolaDto.Nome != null)
            {
                _strSql = _strSql + "AND nome LIKE '%" + _escolaDto.Nome + "%' ";
            }
            if (_escolaDto.Status != 2)
            {
                if (_escolaDto.Status == 1) { _strSql = _strSql + "AND status = 1 "; }
                else { _strSql = _strSql + "AND status = 0 "; }
            }
            try
            {
                dtAdap = new MySqlDataAdapter(_strSql, conn);
                dtAdap.Fill(dtTable);
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return dtTable;
        }

        public EscolaDto ConsultarUmPorParametro(EscolaDto _escolaDto)
        {
            MySqlDataReader dr;
            EscolaDto _escola = new EscolaDto();

            string _strSql = "SELECT * FROM escolas WHERE 1=1 ";

            if (_escolaDto.IdEscola > 0)
            {
                _strSql = _strSql + "AND idescola = " + _escolaDto.IdEscola + " ";
            }

            if (_escolaDto.Nome != string.Empty)
            {
                _strSql = _strSql + "AND nome LIKE '%" + _escolaDto.Nome + "'% ";
            }

            if (_escolaDto.Status != null)
            {
                if (_escolaDto.Status == 1) { _strSql = _strSql + "AND status = 1 "; }
                else { _strSql = _strSql + "AND status = 0 "; }
            }

            try
            {
                cmd.Connection = conn;
                cmd.CommandText = _strSql;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    _escola.IdEscola = Convert.ToInt32(dr["idescola"]);
                    _escola.Nome = dr["nome"].ToString();
                    _escola.Status = Convert.ToInt32(dr["status"]);
                }

            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return _escola;
        }

        public DataTable ConsultarTodos(EscolaDto _escolaDto)
        {
            MySqlDataAdapter dtAdap;
            DataTable dtTable = new DataTable();

            string _strSql = "select idescola, nome, IF(status = 0, 'inativo', 'ativo') as nomestatus  FROM escolas WHERE 1=1 ";
            if (_escolaDto.IdEscola > 0)
            {
                _strSql = _strSql + "AND idescola = " + _escolaDto.IdEscola + " ";
            }
            if (_escolaDto.Nome != string.Empty)
            {
                _strSql = _strSql + "AND nome LIKE '%" + _escolaDto.Nome + "%'";
            }

            if (_escolaDto.Status != 2)
            {
                _strSql = _strSql + "AND status = " + _escolaDto.Status + " ";
            }

            if (_escolaDto.Ordenar == 1)
            {
                _strSql = _strSql + " ORDER BY nome ";
            }
            else
            {
                _strSql = _strSql + " ORDER BY status ";
            }

            try
            {
                dtAdap = new MySqlDataAdapter(_strSql, conn);
                dtAdap.Fill(dtTable);
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return dtTable;
        }

        public DataTable ConsultarTodos(int qtdRegistros)
        {
            MySqlDataAdapter dtAdap;
            DataTable dtTable = new DataTable();

            string _strSql = "select idescola, nome, IF(status = 0, 'inativo', 'ativo') as nomestatus  FROM escolas";
            if (qtdRegistros > 0)
            {
                _strSql = _strSql + " LIMIT " + qtdRegistros.ToString();
            }

            try
            {
                dtAdap = new MySqlDataAdapter(_strSql, conn);
                dtAdap.Fill(dtTable);
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return dtTable;
        }

        public EscolaDto ConsultarSoUm(int idEscola)
        {
            EscolaDto _escolaDto = new EscolaDto();
            string _strSql = "select idescola, nome, status FROM escolas WHERE idescola = " + idEscola + " ";


            try
            {
                cmd.CommandText = _strSql;
                cmd.Connection = conn;

                MySqlDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    _escolaDto.IdEscola = Convert.ToInt32(dr["idescola"]);
                    _escolaDto.Nome = dr["nome"].ToString();
                    _escolaDto.Status = Convert.ToInt32(dr["status"]);
                }

                dr.Close();

            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return _escolaDto;
        }

    }
}